﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class QLTB_DAL:BaseDAL
    {
        public DataTable Select()
        {
            var sql = @"SELECT  *  FROM QUAN_LY_THIET_BI";
            return ExecuteQuery(sql);
        }

        public bool Delete_qltb(string maphong, string matb)
        {
            string sql = @"DELETE FROM QUAN_LY_THIET_BI WHERE MA_PHONG = '{0}' and MA_TB = '{1}'";
            sql = string.Format(sql, maphong, matb);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (QUAN_LY_THIET_BI)obj;
            string sql = @"set dateformat dmy; INSERT INTO QUAN_LY_THIET_BI(MA_PHONG, MA_TB, NGAY_NHAN, SL_NHAN, TT_HOAT_DONG, GHI_CHU) VALUES ('{0}', '{1}', '{2}', '{3}', N'{4}', N'{5}')";
            sql = string.Format(sql, o.MA_PHONG, o.MA_TB, o.NGAY_NHAN, o.SL_NHAN, o.TT_HOAT_DONG, o.GHI_CHU);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (QUAN_LY_THIET_BI)obj;
            string sql = @"set dateformat dmy; UPDATE QUAN_LY_THIET_BI SET SL_NHAN= N'{2}', NGAY_NHAN='{3}' ,TT_HOAT_DONG = N'{4}',  GHI_CHU= N'{5}' WHERE MA_PHONG = '{0}' and MA_TB = '{1}' ";
            sql = string.Format(sql, o.MA_PHONG, o.MA_TB, o.SL_NHAN, o.NGAY_NHAN, o.TT_HOAT_DONG, o.GHI_CHU);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable load_qltb_theo_phong(string maphong)
        {
            var sql = @"
                            select *
                            from QUAN_LY_THIET_BI
                            where
                            MA_PHONG= '{0}' 
                            
                            ";
            sql = string.Format(sql, maphong);
            return ExecuteQuery(sql);
        }
        //IN DANH SACH
        public DataTable dsQLTB(string maphong)
        {
            var sql = @"
                            SELECT TEN_PHONG, TEN_TB, SL_NHAN, NGAY_NHAN, TT_HOAT_DONG,GHI_CHU, hoten_nv
                            FROM QUAN_LY_THIET_BI a, THIET_BI b, PHONG c, nhan_vien d
                            WHERE a.MA_TB = b.MA_TB AND a.MA_PHONG = c.MA_PHONG and b.ma_nv = d.ma_nv and a.MA_PHONG = '{0}'
                            ";
            sql = string.Format(sql, maphong);
            return ExecuteQuery(sql);
        }

        //thong ke thiet bi & hao mon
        public DataTable ds_thongke(DateTime ngay_tk)
        {
            var sql = @"
                            set dateformat dmy;
                                    
                                    select   TEN_PHONG,  TEN_TB, SL_NHAN, ( ( (year('{0}') - year(NGAY_NHAN) ) *   100  ) / SO_NAM_SD) as hao_mon, (100 - (( ( (year('{0}') - year(NGAY_NHAN) ) *   100  ) / SO_NAM_SD)))as con_lai
                                    from		thiet_bi a, QUAN_LY_THIET_BI b, phong c
                                    where		a.MA_TB = b.MA_TB and b.ma_phong = c.ma_phong
                                   
                            ";
            sql = string.Format(sql, ngay_tk);
            return ExecuteQuery(sql);
        }
        //cap nhat lai so luong sau khi them vao phong
        public bool cap_nhat_qltb(int sl, string matb, string maphong)
        {

            var sql = @"set dateformat dmy; UPDATE QUAN_LY_THIET_BI SET  SL_NHAN= '{0}' WHERE MA_TB = '{1}' and MA_PHONG= '{2}' ";
            sql = string.Format(sql, sl, matb, maphong);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
        //kiem tra thiet bi co ton tai trong phong chu?
        public DataTable kt_thietbi(string maphong, string matb)
        {
            var sql = @"
                            select *
                            from QUAN_LY_THIET_BI
                            where
                            MA_PHONG= '{0}'  and  MA_TB = '{1}'
                            
                            ";
            sql = string.Format(sql, maphong, matb);
            return ExecuteQuery(sql);
        }
        //load so luong tu ma
//     
        public string sl_tb(string maphong, string matb)
        {
            var sql = @" set dateformat dmy;select SL_NHAN from QUAN_LY_THIET_BI where  MA_PHONG = '{0}' and MA_TB = '{1}' ";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["SL_NHAN"] + "" : "0";
        }
    }
}
